package org.cem.datasource;

import java.net.URL;
import java.security.NoSuchAlgorithmException;
import java.security.spec.InvalidKeySpecException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.beanutils.DynaBean;
import org.apache.ddlutils.DdlUtilsException;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.io.DatabaseIO;
import org.apache.ddlutils.model.Database;
import org.apache.ddlutils.model.Table;
import org.cem.security.EncryptionUtil;
import org.cem.security.Permission;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

public final class DbAccess {

//	public static final String CEM_DB = "jdbc/cemDB";
	
	private static final String CHANGE_LOG_FILE = "/database/mysql.changelog.xml";
	private static Database db;
	
	static {
		URL resource = DbAccess.class.getResource(CHANGE_LOG_FILE);
		db = new DatabaseIO().read(resource.toString());
	}
	
	private DbAccess() { }
	
	private static DataSource getDataSource() {
		MysqlDataSource ds = new MysqlDataSource();
		ds.setDatabaseName("cemdb");
		ds.setServerName("localhost");
		ds.setPortNumber(3306);
		ds.setUser("cemapp");
		ds.setPassword("cemapp");
		return ds;
//		try {
//			InitialContext context = new InitialContext();
//			return (DataSource)context.lookup(CEM_DB);
//		} catch (NamingException e) {
//			e.printStackTrace();
//			return null;
//		}
	}
	
	public static void initializeDataBase() {
		DataSource ds = getDataSource();
		if (ds != null) {
			try {
				Platform platform = PlatformFactory.createNewPlatformInstance(ds);
				platform.createTables(db, false, true);
			} catch (DdlUtilsException e) {
				e.printStackTrace();
			}
		}
		preserveAdminAccount();
	}
	
	@SuppressWarnings("unchecked")
	public static void preserveAdminAccount() {
		DataSource ds = getDataSource();
		if (ds != null) {
			Platform  platform = PlatformFactory.createNewPlatformInstance(ds);
		    ArrayList<Object> params = new ArrayList<Object>();
		    params.add("admin");
		    
			Iterator<Object> it = platform.query(db,
	             "select * from user where username = ?",
	             params,
	             new Table[] { db.findTable("user") });
		    
		    while (it.hasNext())
		    {
		        DynaBean user = (DynaBean)it.next();
		        user.get("username");
		        return;
		    }
			
		    List<DynaBean> dynaBeans = new ArrayList<>();
		    
		    DynaBean backendPerm = db.createDynaBeanFor("permission", false);
		    backendPerm.set("id", Permission.BACKEND_ACCESS);
		    dynaBeans.add(backendPerm);
		    
		    DynaBean electionPerm = db.createDynaBeanFor("permission", false);
		    electionPerm.set("id", Permission.ELECTION_ACCESS);
		    dynaBeans.add(electionPerm);
		    
		    DynaBean role = db.createDynaBeanFor("role", false);
		    role.set("id", "admin");
		    dynaBeans.add(role);
		    
		    DynaBean permBackend = db.createDynaBeanFor("role_permission", false);
		    permBackend.set("role", "admin");
		    permBackend.set("permission", Permission.BACKEND_ACCESS);
		    dynaBeans.add(permBackend);
		    
		    DynaBean permElection = db.createDynaBeanFor("role_permission", false);
		    permElection.set("role", "admin");
		    permElection.set("permission", Permission.ELECTION_ACCESS);
		    dynaBeans.add(permElection);
		    
	        DynaBean user = db.createDynaBeanFor("user", false);
	        user.set("username", "admin");
	        String encryptPwd = null;
	        try {
				encryptPwd = EncryptionUtil.getEncryptedPasswordBase64("admin");
			} catch (NoSuchAlgorithmException | InvalidKeySpecException e) {
				e.printStackTrace();
				return;
			}
	        user.set("password", encryptPwd);
	        user.set("role", "admin");
	        dynaBeans.add(user);
	        
	        platform.insert(db, dynaBeans);
		}
	}
	
	public static String getPasswordForUser(String username) throws SQLException {
		DataSource ds = getDataSource();
		if (ds != null) {
			Platform  platform = PlatformFactory.createNewPlatformInstance(ds);
		    
			ArrayList<Object> params = new ArrayList<Object>();
		    params.add(username);
		    
			@SuppressWarnings("unchecked")
			Iterator<Object> it = platform.query(db,
	             "select * from user where username = ?",
	             params,
	             new Table[] { db.findTable("user") });
		    
		    while (it.hasNext())
		    {
		        DynaBean user = (DynaBean)it.next();
		        return (String)user.get("password");
		    }
		}
		return null;
	}

	public static String getRoleForUser(String username) {
		DataSource ds = getDataSource();
		if (ds != null) {
			Platform  platform = PlatformFactory.createNewPlatformInstance(ds);
		    
			ArrayList<Object> params = new ArrayList<Object>();
		    params.add(username);
		    
			@SuppressWarnings("unchecked")
			Iterator<Object> it = platform.query(db,
	             "select * from user where username = ?",
	             params,
	             new Table[] { db.findTable("user") });
		    
		    while (it.hasNext())
		    {
		        DynaBean user = (DynaBean)it.next();
		        return (String)user.get("role");
		    }
		}
		return null;
	}

	public static String[] getPermissionsForUser(String username) {
		DataSource ds = getDataSource();
		if (ds != null) {
			Platform  platform = PlatformFactory.createNewPlatformInstance(ds);
		    
			ArrayList<Object> params = new ArrayList<Object>();
		    params.add(username);
		    
			@SuppressWarnings("unchecked")
			Iterator<Object> it = platform.query(db,
	             "select p1.id from user as u1, role as r1, role_permission as rp1, permission as p1 " +
	             "where u1.role = r1.id and r1.id = rp1.role and rp1.permission = p1.id " +
	             "and u1.username = ?",
	             params,
	             new Table[] { db.findTable("permission") });
		    
			List<String> result = new ArrayList<String>();
		    while (it.hasNext())
		    {
		        DynaBean user = (DynaBean)it.next();
		        result.add((String)user.get("id"));
		    }
		    return result.toArray(new String[result.size()]);
		}
		return null;
	}
}
